/*
 * DB.java
 * 
 * This file is part of Netuno Psamata.
 * 
 * CajuScript is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3, or (at your option) 
 * any later version.
 * 
 * CajuScript is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with CajuScript.  If not, see <http://www.gnu.org/licenses/>.
*/

package org.netuno.psamata;

import java.io.IOException;
import java.sql.Clob;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.ArrayList;
import java.sql.Blob;
import java.sql.Date;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;

/**
 * DB.
 * @author eduveks
 */
public class DB {
    /**
     * Conecction.
     */
    private Connection con = null;
    /**
     * Statement.
     */
    private Statement stat = null;
    /**
     * DB.
     * @param connection DB Conection
     * @throws SQLException Create Statement Exception
     */
    public DB(final Connection connection) throws SQLException {
        this.con = connection;
        this.stat = this.con.createStatement();
    }
    /**
     * Sql Injection.
     * @param text Text
     * @return Text ok to use in sql querys
     */
    public static String sqlInjectionRaw(final String text) throws PsamataException {
    	if (text.matches("^[a-z_]+[a-z0-9_\\.]*$")) {
    		return text;
    	}
    	throw new PsamataException("SQL Injection with value "+ text);
    }
    /**
     * Sql Injection.
     * @param text Text
     * @return Text ok to use in sql querys
     */
    public static String sqlInjectionSyntax(final String text) throws PsamataException {
    	if (text.matches("^[a-z_]+[a-z0-9_]*$")) {
    		return text;
    	}
    	throw new PsamataException("SQL Injection with value "+ text);
    }
    /**
     * Sql Injection.
     * @param text Text
     * @return Text ok to use in sql querys
     */
    public static String sqlInjection(final String text) {
        return text.replace((CharSequence)"'", (CharSequence)"''");
    }

    /**
     * Sql injection integer.
     * @param text Text
     * @return Text ok to use in sql querys
     */
    public static String sqlInjectionInt(final String text) {
        try {
            return Integer.toString(Integer.parseInt(text));
        } catch (Exception e) {
            return "0";
        }
    }
    
    /**
     * Sql injection sequence integer.
     * @param text Text
     * @return Text ok to use in sql querys
     */
    public static String sqlInjectionIntSequence(final String text) {
        try {
        	String[] values = text.split("\\,");
        	String result = "";
        	for (String value : values) {
        		if (!result.isEmpty()) {
        			result = result.concat(", ");
        		}
        		result = result.concat(Integer.toString(Integer.parseInt(value.trim())));
        	}
        	return result;
        } catch (Exception e) {
            return "";
        }
    }
    
    /**
     * Sql injection list integer.
     * @param text Text
     * @return Text ok to use in sql querys
     */
    public static List<String> sqlInjectionIntList(final String text) {
        try {
        	String[] values = text.split("\\,");
        	List<String> result = new ArrayList<String>();
        	for (int i = 0; i < values.length; i++) {
        		result.add(Integer.toString(Integer.parseInt(values[i].trim())));
        	}
        	return result;
        } catch (Exception e) {
            return null;
        }
    }
    
    /**
     * Sql injection float.
     * @param text Text
     * @return Text ok to use in sql querys
     */
    public static String sqlInjectionFloat(final String text) {
        try {
            return Float.toString(Float.parseFloat(text));
        } catch (Exception e) {
            return "0";
        }
    }
    /**
     * Execute Query.
     * @param query Query
     * @return Result
     * @throws SQLException SQL Exception
     */
    public synchronized List<Values> executeQuery(final String query
    ) throws SQLException {
        return executeQuery(con, query);
    }
    /**
     * Execute Query.
     * @param con Connection
     * @param query Query
     * @return Result
     * @throws SQLException SQL Exception
     */
    public static synchronized List<Values> executeQuery(final Connection con, final String query) throws SQLException {
        List<Values> datasource = new ArrayList<Values>();
        Statement stat = null;
        ResultSet rs = null;
        try {
            stat = con.createStatement();
            rs = stat.executeQuery(query);
            while (rs.next()) {
                datasource.add(getValues(rs));
            }
        } catch (SQLException e) {
            throw e;
        } catch (Exception e) {
            throw new Error(e);
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (stat != null) {
                stat.close();
            }
        }
        return datasource;
    }
    /**
     * Execute Query.
     * @param con Connection
     * @param query Query
     * @param params Parameters
     * @return Result
     * @throws SQLException SQL Exception
     */
    public static synchronized List<Values> executeQuery(final Connection con, final String query, final Object[] params) throws SQLException {
        List<Values> datasource = new ArrayList<Values>();
        PreparedStatement stat = null;
        ResultSet rs = null;
        try {
            stat = con.prepareStatement(query);
            prepareStatementParams(stat, params);
            rs = stat.executeQuery();
            while (rs.next()) {
                datasource.add(getValues(rs));
            }
        } catch (SQLException e) {
            throw e;
        } catch (Exception e) {
            throw new Error(e);
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (stat != null) {
                stat.close();
            }
        }
        return datasource;
    }
    /**
     * Get Values from current row of a ResultSet
     * @param rs ResultSet
     * @return Values with data row
     * @throws SQLException
     * @throws IOException
     */
    public static Values getValues(ResultSet rs) throws SQLException, IOException {
    	Values row = new Values();
        for (int x = 1; x <= rs.getMetaData().getColumnCount(); x++) {
            Object o = null;
            if (rs.getMetaData().getColumnType(x) == java.sql.Types.CLOB) {
                Clob clob = rs.getClob(x);
                if (clob != null) {
                    o = org.netuno.psamata.io.InputStream.readAll(clob.getCharacterStream());
                }
            } else if (rs.getMetaData().getColumnType(x) == java.sql.Types.BLOB) {
                Blob blob = rs.getBlob(x);
                if (blob != null) {
                    o = org.netuno.psamata.io.InputStream.readAll(blob.getBinaryStream());
                }
            } else {
                o = rs.getObject(x);
            }
            row.set(rs.getMetaData().getColumnLabel(x), o);
        }
        return row;
    }
    /**
     * Execute Update Query.
     * @param con Connection
     * @param query Query
     * @return Result
     * @throws SQLException SQL Exception
     */
    public static int execute(final Connection con,
    final String query
    ) throws SQLException {
        Statement stat = con.createStatement();
        int result = stat.executeUpdate(query);
        stat.close();
        return result;
    }
    /**
     * Execute Update Query.
     * @param con Connection
     * @param query Query
     * @param params Parameters
     * @return Result
     * @throws SQLException SQL Exception
     */
    public static int execute(final Connection con, final String sql, final Object[] params) throws SQLException {
    	PreparedStatement stat = con.prepareStatement(sql);
    	prepareStatementParams(stat, params);
        int result = stat.executeUpdate();
        stat.close();
        return result;
    }
    /**
     * Execute Update Query.
     * @param query Query
     * @return Result
     * @throws SQLException SQL Exception
     */
    public final int execute(final String query) throws SQLException {
        return stat.executeUpdate(query);
    }
    /**
     * Close DB Connection.
     * @throws SQLException Close Connection Exception
     */
    public final void close() throws SQLException {
        stat.close();
    }
    
    private static void prepareStatementParams(PreparedStatement stat, Object[] params) throws SQLException {
    	for (int i = 1; i <= params.length; i++) {
    		Object param = params[i - 1];
    		if (param instanceof String) {
    			stat.setString(i, (String)param);
    		} else if (param instanceof Integer) {
    			stat.setInt(i, (Integer)param);
    		} else if (param instanceof Boolean) {
    			stat.setBoolean(i, (Boolean)param);
    		} else if (param instanceof Short) {
    			stat.setShort(i, (Short)param);
    		} else if (param instanceof Long) {
    			stat.setLong(i, (Long)param);
    		} else if (param instanceof Double) {
    			double doubleValue = ((Double)param).doubleValue();
    			int intValue = (int)doubleValue;
    			if (intValue == doubleValue) {
        			stat.setInt(i, intValue);
    			} else {
    				stat.setDouble(i, doubleValue);
    			}
    		} else if (param instanceof Float) {
    			float floatValue = ((Float)param).floatValue();
    			int intValue = (int)floatValue;
    			if (intValue == floatValue) {
        			stat.setInt(i, intValue);
    			} else {
    				stat.setFloat(i, floatValue);
    			}
    		} else if (param instanceof java.util.Date) {
    			stat.setDate(i, new java.sql.Date(((java.util.Date)param).getTime()), new GregorianCalendar());
    		} else if (param instanceof java.util.Calendar) {
    			stat.setDate(i, new java.sql.Date(((java.util.Calendar)param).getTimeInMillis()), (java.util.Calendar)param);
    		} else if (param instanceof Date) {
    			stat.setDate(i, (Date)param);
    		} else if (param instanceof Time) {
    			stat.setTime(i, (Time)param);
    		} else if (param instanceof Timestamp) {
				stat.setTimestamp(i, (Timestamp)param, new GregorianCalendar());
    		} else if (param instanceof Object) {
				stat.setObject(i, (Object)param);
			}
    	}
    }
}